{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "844205d7-65af-4164-bdf5-1a6db15f8927",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install arcticdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "395e7b38-6e1f-495f-8940-87462c6e8eb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import arcticdb as adb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c848d76-a292-45a9-b297-314b120c0d47",
   "metadata": {},
   "source": [
    "<center><img src=\"\"/>\n",
    "</center>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66fd9cae-39d0-4555-9c08-9f443bce8894",
   "metadata": {},
   "source": [
    "# ArcticDB Resample Demo\n",
    "\n",
    "This demo notebook showcases the high-performance resample capability of ArcticDB.\n",
    "\n",
    "This is what you need to know about it:\n",
    "\n",
    "* It runs on-the-fly as part of the read\n",
    "* This makes it much more efficient than Pandas on large datasets\n",
    "* The usage is similar to the Pandas resample function\n",
    "* You can apply multiple aggregators to each column\n",
    "* It can be used for downsampling high frequency data and generating \"bar\" data (see example 4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae923ddb-02ed-49a9-aa47-a87cdf0ccff7",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "85d940e6-c8f5-4080-87a7-f8a635960c40",
   "metadata": {},
   "outputs": [],
   "source": [
    "# object store\n",
    "arctic = adb.Arctic(\"lmdb://arcticdb_resample\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b29f9e15-88de-4d3d-b461-82e560249b54",
   "metadata": {},
   "outputs": [],
   "source": [
    "# library\n",
    "lib = arctic.get_library('resample', create_if_missing=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "652ae833-b07d-4770-9dd5-fb9065816d50",
   "metadata": {},
   "source": [
    "## Create Some Data\n",
    "\n",
    "* timeseries with 12,000,000 rows and a 1-second index\n",
    "* int, float, string columns\n",
    "* write the data into ArcticDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "64b0c0d9-ceb2-486e-9511-c02984cc7054",
   "metadata": {},
   "outputs": [],
   "source": [
    "# data for resampling\n",
    "index = pd.date_range(\"1990-01-01\", periods=12_000_000, freq=\"s\")\n",
    "int_data = np.arange(len(index), dtype=np.uint64)\n",
    "float_data = np.round(np.random.uniform(95., 105., len(index)), 3)\n",
    "letters = ['a','b','c','d','e','f','g']\n",
    "mkt_data = pd.DataFrame(\n",
    "    index=index,\n",
    "    data={\n",
    "        \"id\": int_data,\n",
    "        \"price\": float_data,\n",
    "        \"category\": (letters*(len(index)//len(letters) + 1))[:len(index)]\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3cc7b33f-ecb0-4588-a9ee-0bfaa4de42e1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>price</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:00:00</th>\n",
       "      <td>0</td>\n",
       "      <td>95.176</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:00:01</th>\n",
       "      <td>1</td>\n",
       "      <td>97.872</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:00:02</th>\n",
       "      <td>2</td>\n",
       "      <td>104.930</td>\n",
       "      <td>c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:00:03</th>\n",
       "      <td>3</td>\n",
       "      <td>103.573</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:00:04</th>\n",
       "      <td>4</td>\n",
       "      <td>97.052</td>\n",
       "      <td>e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:00:05</th>\n",
       "      <td>5</td>\n",
       "      <td>103.435</td>\n",
       "      <td>f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:00:06</th>\n",
       "      <td>6</td>\n",
       "      <td>99.339</td>\n",
       "      <td>g</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:00:07</th>\n",
       "      <td>7</td>\n",
       "      <td>103.358</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:00:08</th>\n",
       "      <td>8</td>\n",
       "      <td>104.301</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:00:09</th>\n",
       "      <td>9</td>\n",
       "      <td>104.651</td>\n",
       "      <td>c</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     id    price category\n",
       "1990-01-01 00:00:00   0   95.176        a\n",
       "1990-01-01 00:00:01   1   97.872        b\n",
       "1990-01-01 00:00:02   2  104.930        c\n",
       "1990-01-01 00:00:03   3  103.573        d\n",
       "1990-01-01 00:00:04   4   97.052        e\n",
       "1990-01-01 00:00:05   5  103.435        f\n",
       "1990-01-01 00:00:06   6   99.339        g\n",
       "1990-01-01 00:00:07   7  103.358        a\n",
       "1990-01-01 00:00:08   8  104.301        b\n",
       "1990-01-01 00:00:09   9  104.651        c"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view the first 10 rows of the data\n",
    "mkt_data.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9811863f-4d77-4571-b84b-95b341751fcd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VersionedItem(symbol='market_data', library='resample', data=n/a, version=0, metadata=None, host='LMDB(path=~/arcticdb_resample)', timestamp=1718958796318913629)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# write the data into ArcticDB\n",
    "sym = 'market_data'\n",
    "lib.write(sym, mkt_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ece26c4e-826f-48b1-918c-f2b1aff01ad4",
   "metadata": {},
   "source": [
    "## 1. Simple Resample\n",
    "\n",
    "* Resample to 1-minute\n",
    "* Use different aggregators\n",
    "* Resample can be thought of as a time-based groupby\n",
    "* The groups are all the rows within a time interval\n",
    "* Run also in Pandas to compare performance and results "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c05c6a38-5326-407d-9588-8c562c5f0f66",
   "metadata": {},
   "outputs": [],
   "source": [
    "# frequency and aggregator params\n",
    "freq1 = '1min'\n",
    "aggs1 = {'id': 'max', 'price': 'last', 'category': 'count'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1829312c-24ee-4da7-959d-a5465456fdcc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "200000\n",
      "CPU times: user 684 ms, sys: 251 ms, total: 935 ms\n",
      "Wall time: 171 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>price</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:15:00</th>\n",
       "      <td>11999759</td>\n",
       "      <td>104.106</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:16:00</th>\n",
       "      <td>11999819</td>\n",
       "      <td>104.456</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:17:00</th>\n",
       "      <td>11999879</td>\n",
       "      <td>95.570</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:18:00</th>\n",
       "      <td>11999939</td>\n",
       "      <td>103.967</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:19:00</th>\n",
       "      <td>11999999</td>\n",
       "      <td>97.899</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           id    price  category\n",
       "1990-05-19 21:15:00  11999759  104.106        60\n",
       "1990-05-19 21:16:00  11999819  104.456        60\n",
       "1990-05-19 21:17:00  11999879   95.570        60\n",
       "1990-05-19 21:18:00  11999939  103.967        60\n",
       "1990-05-19 21:19:00  11999999   97.899        60"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "# create the resample query and apply it on the read\n",
    "market_data_1min_df = lib.read(sym, lazy=True).resample(freq1).agg(aggs1).collect().data\n",
    "print(len(market_data_1min_df))\n",
    "market_data_1min_df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c8bfcc79-8cd6-4b13-aca9-cf52f4c56042",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "200000\n",
      "CPU times: user 1.6 s, sys: 401 ms, total: 2 s\n",
      "Wall time: 1.15 s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>price</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:15:00</th>\n",
       "      <td>11999759</td>\n",
       "      <td>104.106</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:16:00</th>\n",
       "      <td>11999819</td>\n",
       "      <td>104.456</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:17:00</th>\n",
       "      <td>11999879</td>\n",
       "      <td>95.570</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:18:00</th>\n",
       "      <td>11999939</td>\n",
       "      <td>103.967</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:19:00</th>\n",
       "      <td>11999999</td>\n",
       "      <td>97.899</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           id    price  category\n",
       "1990-05-19 21:15:00  11999759  104.106        60\n",
       "1990-05-19 21:16:00  11999819  104.456        60\n",
       "1990-05-19 21:17:00  11999879   95.570        60\n",
       "1990-05-19 21:18:00  11999939  103.967        60\n",
       "1990-05-19 21:19:00  11999999   97.899        60"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "# read the full data set and resample in Pandas\n",
    "full_df = lib.read(sym).data\n",
    "market_data_1min_pd_df = full_df.resample(freq1).agg(aggs1)\n",
    "print(len(market_data_1min_pd_df))\n",
    "market_data_1min_pd_df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e83c9467-e0d4-447c-bf1e-2ff91bb724ad",
   "metadata": {},
   "source": [
    "## 2. Multiple Aggregators per Column\n",
    "\n",
    "* Similar to NamedAgg in Pandas\n",
    "* Downsample to 5-minute frequency\n",
    "* Apply both max and last aggregators to the price column\n",
    "* For multiple aggregators, the syntax is `output_column_name: (input_column_name: aggregator)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "70d3765a-f8c8-4cf3-beaa-2cc7a469b358",
   "metadata": {},
   "outputs": [],
   "source": [
    "freq2 = '5min'\n",
    "aggs2 = {'id': 'max', 'price_last': ('price' ,'last'), 'price_count': ('price' ,'count'), 'category': 'first'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "8c31ec4b-8454-469d-8e31-8fefd0059bac",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.07 s, sys: 415 ms, total: 1.49 s\n",
      "Wall time: 151 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>category</th>\n",
       "      <th>price_count</th>\n",
       "      <th>price_last</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:00:00</th>\n",
       "      <td>299</td>\n",
       "      <td>a</td>\n",
       "      <td>300</td>\n",
       "      <td>102.172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:05:00</th>\n",
       "      <td>599</td>\n",
       "      <td>g</td>\n",
       "      <td>300</td>\n",
       "      <td>101.450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:10:00</th>\n",
       "      <td>899</td>\n",
       "      <td>f</td>\n",
       "      <td>300</td>\n",
       "      <td>96.718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:15:00</th>\n",
       "      <td>1199</td>\n",
       "      <td>e</td>\n",
       "      <td>300</td>\n",
       "      <td>96.345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:20:00</th>\n",
       "      <td>1499</td>\n",
       "      <td>d</td>\n",
       "      <td>300</td>\n",
       "      <td>98.955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 20:55:00</th>\n",
       "      <td>11998799</td>\n",
       "      <td>d</td>\n",
       "      <td>300</td>\n",
       "      <td>100.277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:00:00</th>\n",
       "      <td>11999099</td>\n",
       "      <td>c</td>\n",
       "      <td>300</td>\n",
       "      <td>103.596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:05:00</th>\n",
       "      <td>11999399</td>\n",
       "      <td>b</td>\n",
       "      <td>300</td>\n",
       "      <td>96.182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:10:00</th>\n",
       "      <td>11999699</td>\n",
       "      <td>a</td>\n",
       "      <td>300</td>\n",
       "      <td>99.911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-19 21:15:00</th>\n",
       "      <td>11999999</td>\n",
       "      <td>g</td>\n",
       "      <td>300</td>\n",
       "      <td>97.899</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>40000 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           id category  price_count  price_last\n",
       "1990-01-01 00:00:00       299        a          300     102.172\n",
       "1990-01-01 00:05:00       599        g          300     101.450\n",
       "1990-01-01 00:10:00       899        f          300      96.718\n",
       "1990-01-01 00:15:00      1199        e          300      96.345\n",
       "1990-01-01 00:20:00      1499        d          300      98.955\n",
       "...                       ...      ...          ...         ...\n",
       "1990-05-19 20:55:00  11998799        d          300     100.277\n",
       "1990-05-19 21:00:00  11999099        c          300     103.596\n",
       "1990-05-19 21:05:00  11999399        b          300      96.182\n",
       "1990-05-19 21:10:00  11999699        a          300      99.911\n",
       "1990-05-19 21:15:00  11999999        g          300      97.899\n",
       "\n",
       "[40000 rows x 4 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "lib.read(sym, lazy=True).resample(freq2).agg(aggs2).collect().data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82ee0316-73a6-41e0-92c6-04c22cc449bb",
   "metadata": {},
   "source": [
    "## 3. Processing Pipeline: Chaining Operations\n",
    "\n",
    "* Downsample to 2.5-minutes frequency\n",
    "* Group the resampled data by the category column\n",
    "* Aggregate the category groups using mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8d597ad7-56e7-4d11-830b-e77e0b3671f6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.12 s, sys: 309 ms, total: 1.43 s\n",
      "Wall time: 183 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>5999700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>5999925.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>5999850.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>6000075.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>5999775.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>g</th>\n",
       "      <td>6000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>6000150.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id\n",
       "category           \n",
       "a         5999700.0\n",
       "b         5999925.0\n",
       "d         5999850.0\n",
       "e         6000075.0\n",
       "f         5999775.0\n",
       "g         6000000.0\n",
       "c         6000150.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "lib.read(sym, lazy=True).resample('2min30s').agg({'id': 'min', 'category': 'first'}).groupby('category').agg({'id': 'mean'}.collect().data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3a9268e-f843-4bd6-a2f2-b4f51b50f0ce",
   "metadata": {},
   "source": [
    "## 4. Example: OHLC (Open High Low Close) Bars\n",
    "\n",
    "* Downsample to 5-minute frequency\n",
    "* Use multiple aggregators on the price column\n",
    "* This is a simple example of how to convert tick data to OHLC bar data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5e1bccab-77a9-42fa-9c33-60486498355f",
   "metadata": {},
   "outputs": [],
   "source": [
    "freq_ohlc = '5min'\n",
    "agg_ohlc = {\n",
    "    'open': ('price', 'first'),\n",
    "    'high': ('price', 'max'),\n",
    "    'low': ('price', 'min'),\n",
    "    'close': ('price', 'last')\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "5a5abb8e-2de9-48a9-a0a7-7bd044a8064f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.26 s, sys: 492 ms, total: 1.75 s\n",
      "Wall time: 118 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "ohlc_5min_bars = lib.read(sym, lazy=True).resample(freq_ohlc).agg(agg_ohlc).collect().data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "25d0d145-5f93-45ab-86ca-12e34c2be8c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>close</th>\n",
       "      <th>low</th>\n",
       "      <th>high</th>\n",
       "      <th>open</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:00:00</th>\n",
       "      <td>102.172</td>\n",
       "      <td>95.076</td>\n",
       "      <td>104.992</td>\n",
       "      <td>95.176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:05:00</th>\n",
       "      <td>101.450</td>\n",
       "      <td>95.008</td>\n",
       "      <td>104.999</td>\n",
       "      <td>98.520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:10:00</th>\n",
       "      <td>96.718</td>\n",
       "      <td>95.053</td>\n",
       "      <td>104.990</td>\n",
       "      <td>103.959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:15:00</th>\n",
       "      <td>96.345</td>\n",
       "      <td>95.070</td>\n",
       "      <td>104.969</td>\n",
       "      <td>95.878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-01-01 00:20:00</th>\n",
       "      <td>98.955</td>\n",
       "      <td>95.011</td>\n",
       "      <td>104.983</td>\n",
       "      <td>103.538</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       close     low     high     open\n",
       "1990-01-01 00:00:00  102.172  95.076  104.992   95.176\n",
       "1990-01-01 00:05:00  101.450  95.008  104.999   98.520\n",
       "1990-01-01 00:10:00   96.718  95.053  104.990  103.959\n",
       "1990-01-01 00:15:00   96.345  95.070  104.969   95.878\n",
       "1990-01-01 00:20:00   98.955  95.011  104.983  103.538"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ohlc_5min_bars.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2180f79-3792-4642-8e8b-51b9c41f7345",
   "metadata": {},
   "source": [
    "## Conclusion\n",
    "\n",
    "We have demonstrated the following about the ArcticDB resample feature:\n",
    "\n",
    "* Easy to use, especially if you already resample in Pandas\n",
    "* Very high performance - in particular much faster than reading all the data then resampling in Pandas\n",
    "* Can be combined with other query functions to build processing pipelines\n",
    "* Can be used to generate timeseries bars"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
